package com.yuyou.activity.group.service.service.impl;

import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import com.yuyou.activity.group.service.vo.resp.*;
import com.yuyou.activity.util.bean.Pagination;
import com.yuyou.activity.util.util.*;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.hutool.core.exceptions.ExceptionUtil;

import com.yuyou.activity.comm.service.pay.service.IZyPayService;
import com.yuyou.activity.comm.service.vo.req.ReqCreateOrder;
import com.yuyou.activity.comm.service.vo.req.ReqRufundOrder;
import com.yuyou.activity.group.service.ActivityGroupSPConfig;
import com.yuyou.activity.group.service.entity.ActivityGroupbuy;
import com.yuyou.activity.group.service.entity.ActivityGroupbuyGoods;
import com.yuyou.activity.group.service.entity.ActivityGroupbuyOrder;
import com.yuyou.activity.group.service.entity.ActivityGroupbuyOrderDetail;
import com.yuyou.activity.group.service.entity.ActivityGroupbuyUser;
import com.yuyou.activity.group.service.service.IActivityGroupSPService;
import com.yuyou.activity.group.service.service.IActivityGroupbuyGoodsService;
import com.yuyou.activity.group.service.service.IActivityGroupbuyOrderDetailService;
import com.yuyou.activity.group.service.service.IActivityGroupbuyOrderService;
import com.yuyou.activity.group.service.service.IActivityGroupbuyService;
import com.yuyou.activity.group.service.service.IActivityGroupbuyUserService;
import com.yuyou.activity.group.service.vo.req.ReqActivityGroupSPSubOrder;
import com.yuyou.activity.util.bean.ResBoolSimpleInfo;
import com.yuyou.activity.util.bean.ReturnResult;
import com.yuyou.activity.util.enums.ActivityGroupBuyEnums;
import com.yuyou.activity.util.enums.ActivityGroupBuyOrderEnums;
import com.yuyou.activity.util.exception.BusinessException;

@Service
public class ActivitySPServiceImpl implements IActivityGroupSPService {
	private final Logger logger = LoggerFactory.getLogger(this.getClass());

	@Value("${shipin.importVipUserAndAddCourse.url}")
	private String SHI_PIN_URL;

	@Value("${shipin.importVipUserAndAddCourse.key}")
	private String SHI_PIN_KEY;

	@Value("${add.courseVip.appKey}")
	private String AppKey;

	public static final String DEFAULT_LOCATION = "广东 广州";

	public static final String DEFAULT_SCHOOL = "未知";

	public static final String DEFAULT_GRADE_NAME = "高二";

	@Autowired
	private IActivityGroupbuyService actGroupbuyService;

	@Autowired
	private IActivityGroupbuyUserService actGroupbuyUserService;

	@Autowired
	private IActivityGroupbuyOrderService actGroupbuyOrdService;

	@Autowired
	private IActivityGroupbuyOrderDetailService actGrobuyOrdDetService;

	@Autowired
	private IActivityGroupbuyGoodsService activityGroupbuyGoodsService;

	@Autowired
	private IZyPayService zyPayService;

	@Override
	public ReturnResult<String> synchUserOrder2Shipin(String name,
			String phone, String courseIds) {

		Map<String, String> params = new HashMap<>();

		params.put("name", name.trim());
		params.put("phoneNo", phone.trim());
		params.put("courseIds", courseIds.trim());
		params.put("location", ActivitySPServiceImpl.DEFAULT_LOCATION.trim());
		params.put("school", ActivitySPServiceImpl.DEFAULT_SCHOOL.trim());
		params.put("gradeName", ActivitySPServiceImpl.DEFAULT_GRADE_NAME.trim());
		params.put("key", SHI_PIN_KEY.trim());

		String _sign = ZyPaySignUtil.sign(params, AppKey);// 验签
		params.put("sign", _sign);

		try {
			LogUtil.info(logger , "开始同步视频vip至shipin.com ( name : " + name +  "、 phone: " + phone + " 、  courseIds ： " + courseIds);
			String resposeStr = HttpClient.post(SHI_PIN_URL, params, null);
			if (resposeStr == null || !"OK".equalsIgnoreCase(resposeStr)) {
				//去掉空格换行符等
				resposeStr = StrUtil.replaceBlank(resposeStr);
				LogUtil.error(logger , "数据同步失败，返回：" + resposeStr);
				return ReturnResult.fail("数据同步失败，返回：" + resposeStr);
			}
		} catch (Exception e) {
			LogUtil.error(logger , ExceptionUtil.getMessage(e));
			return ReturnResult.fail(e.getMessage());
		}

		return ReturnResult.success("OK");
	}


	@Transactional(rollbackFor = Exception.class)
	@Override
	public ResBoolSimpleInfo<String> submitOrder(
			ReqActivityGroupSPSubOrder reqActivityGroupSPSubOrder)
			throws Exception {

		String goodsName=StringUtils.EMPTY;
		//校验所有商品是否合法
		for (Long goodsId : reqActivityGroupSPSubOrder.getActivityGroupbuyGoodsIds()) {
			ActivityGroupbuyGoods goods = activityGroupbuyGoodsService.selectByPrimaryKey(goodsId);
			if(goods==null){
				throw new BusinessException("非法请求");
			}
			goodsName=goodsName+goods.getName()+" ";
		}

		// 获取用户信息
		ActivityGroupbuyUser user = actGroupbuyUserService.addOrUpUser(
				reqActivityGroupSPSubOrder.getOpenId(),
				reqActivityGroupSPSubOrder.getWxImg(),
				reqActivityGroupSPSubOrder.getWxUserName(),
				reqActivityGroupSPSubOrder.getProjectCode());

		// 判断用户历史订单(手机号)是否含有该科目
		List<ActivityGroupbuyOrderDetail> orderDetailList = actGrobuyOrdDetService
				.findSuccByOpenId7Phone(reqActivityGroupSPSubOrder.getProjectCode(),reqActivityGroupSPSubOrder.getOpenId(),reqActivityGroupSPSubOrder.getPhone());
		Set<Long> goodsSet = orderDetailList.stream()
				.map(item -> item.getActivityGroupbuyGoodsId())
				.collect(Collectors.toSet());
		if(!goodsSet.isEmpty()){
			for (Long goodsId : reqActivityGroupSPSubOrder.getActivityGroupbuyGoodsIds()) {
				if (goodsSet.contains(goodsId)) {
					ActivityGroupbuyGoods activityGroupbuyGoods = activityGroupbuyGoodsService
							.selectByPrimaryKey(goodsId);
					return ResBoolSimpleInfo.getFailInfo("你已经购买过"
							+ activityGroupbuyGoods.getName() + "，请勿重复购买");
				}
			}			
		}


		//团购类型：0：单人买，1：团购
		Integer groupType = null;
		Long acticityId=null;
		//类型：0：团主，1：团员
		Integer type = null;
		if (reqActivityGroupSPSubOrder.getActivityGroupbuyId() != null) {
						
			// 参团
			ActivityGroupbuy activityGroupbuy = actGroupbuyService
					.selectByPrimaryKey(reqActivityGroupSPSubOrder
							.getActivityGroupbuyId());
			
			if(activityGroupbuy.getGroupType().intValue()==ActivityGroupBuyEnums.groupType.SINGLE.getValue()){
				return ResBoolSimpleInfo.getFailInfo("该团是单人团");
			}
			
			if (activityGroupbuy.getStatus().intValue() == ActivityGroupBuyEnums.status.TRUE_SUCCESS
					.getValue()
					|| activityGroupbuy.getStatus().intValue() == ActivityGroupBuyEnums.status.FAKE_SUCCESS
							.getValue()) {
				return ResBoolSimpleInfo.getFailInfo("该团已满");
			}
			if (activityGroupbuy.getStatus().intValue() == ActivityGroupBuyEnums.status.FAIL
					.getValue()) {
				return ResBoolSimpleInfo.getFailInfo("该团已失效");
			}
			acticityId = reqActivityGroupSPSubOrder.getActivityGroupbuyId();
			groupType = activityGroupbuy.getGroupType();
			type=ActivityGroupBuyOrderEnums.type.MEMBER.getValue();
			// 假如满3人，更新团状态（支付通知控制）

		} else {
			// 开团
			
			//开团时间控制
			if(ActivityGroupSPConfig.getConfig(reqActivityGroupSPSubOrder.getProjectCode()).getOpenClose().getTime()<new Date().getTime()){
				throw new BusinessException("不能开团");
			}
			
			ActivityGroupbuy activityGroupbuy = new ActivityGroupbuy();
			activityGroupbuy.setProjectCode(reqActivityGroupSPSubOrder
					.getProjectCode());
			activityGroupbuy.setActivityGroupbuyUserId(user
					.getActivityGroupbuyUserId());
			activityGroupbuy.setType(1);
			activityGroupbuy.setGroupType(reqActivityGroupSPSubOrder
					.getGroupType());
			activityGroupbuy.setTotal(ActivityGroupSPConfig.getConfig(
					reqActivityGroupSPSubOrder.getProjectCode()).getNum());
			
			Date now=new Date();
			activityGroupbuy.setCreateTime(now);
			
			//设置结束时间
			Calendar cal = Calendar.getInstance();   
	        cal.setTime(now);   
	        cal.add(Calendar.HOUR, ActivityGroupSPConfig.getConfig(reqActivityGroupSPSubOrder.getProjectCode()).getCloseHour());
	        Date closeDate = cal.getTime();  		
	        activityGroupbuy.setCloseTime(closeDate);
			
			actGroupbuyService.add(activityGroupbuy);
			acticityId = activityGroupbuy.getActivityGroupbuyId();
			groupType = activityGroupbuy.getGroupType();
			type=ActivityGroupBuyOrderEnums.type.MASTER.getValue();
		}

		// 创建活动订单
		ActivityGroupbuyOrder order = new ActivityGroupbuyOrder();
		order.setActivityGroupbuyId(acticityId);
		order.setActivityGroupbuyUserId(user.getActivityGroupbuyUserId());
		order.setProjectCode(reqActivityGroupSPSubOrder.getProjectCode());
		order.setPhone(reqActivityGroupSPSubOrder.getPhone());
		order.setName(reqActivityGroupSPSubOrder.getName());
		order.setChannel(reqActivityGroupSPSubOrder.getChannel());		
		order.setType(type);
		actGroupbuyOrdService.add(order);

		// 创建订单明细
		for (Long goodId : reqActivityGroupSPSubOrder.getActivityGroupbuyGoodsIds()) {
			ActivityGroupbuyOrderDetail orderDetail = new ActivityGroupbuyOrderDetail();
			orderDetail.setActivityGroupbuyOrderId(order
					.getActivityGroupbuyOrderId());
			orderDetail.setActivityGroupbuyGoodsId(goodId);
			orderDetail.setProjectCode(reqActivityGroupSPSubOrder
					.getProjectCode());
			actGrobuyOrdDetService.add(orderDetail);
		}

		//计算订单总额
		RespCalPrice price=calculate(reqActivityGroupSPSubOrder.getProjectCode(),groupType,reqActivityGroupSPSubOrder.getActivityGroupbuyGoodsIds());

		// 创建支付中心订单
		ReqCreateOrder reqCreateOrder = new ReqCreateOrder();
		reqCreateOrder.setAmount(String.valueOf(price.getDiscount()));
		reqCreateOrder.setOrderContent(goodsName);
		reqCreateOrder.setOrderId(order.getOrderId());
		reqCreateOrder.setOrderName(ActivityGroupSPConfig.getConfig(order.getProjectCode()).getOrderGoodName());
		reqCreateOrder.setProjectCode(order.getProjectCode());
		
		//转码url
		String url=reqActivityGroupSPSubOrder.getPaySuccUrl()+"?activityGroupbuyId="+acticityId;
		reqCreateOrder.setReturnUrl(url);
		reqCreateOrder.setSysCode(ActivityGroupSPConfig.getConfig(order.getProjectCode()).getSysCode());
		reqCreateOrder.setUserId(String.valueOf(order.getActivityGroupbuyUserId()));
		ResBoolSimpleInfo<String> res=zyPayService.createOrder(reqCreateOrder);

		return res;
	}
	
	@Override
	public RespCalPrice calculate(String projectCode,int groupType, Set<Long> goodsIds) {	
		//打包价
		String pack=ActivityGroupSPConfig.getConfig(projectCode).getPackDiscount();
		
		Long origin = new Long(0);
		Long discount = new Long(0);
		// 算价格
		int goodsNum = goodsIds.size();
		if (groupType == ActivityGroupBuyEnums.groupType.SINGLE.getValue()) {
			// 单人买
			for (Long goodsId : goodsIds) {
				ActivityGroupbuyGoods goods = activityGroupbuyGoodsService
						.selectByPrimaryKey(goodsId);
				origin = origin + goods.getsPrice();
			}
			
			if ("1".equals(pack)&&goodsNum > 3) {
				discount = Long.valueOf(ActivityGroupSPConfig.getConfig(projectCode).getsDiscount3())  / 3 * goodsNum;
				//除以100，向下取整，再乘以100
				discount = new BigDecimal(discount/100).setScale(0 , BigDecimal.ROUND_DOWN).longValue() * 100;
//				discount = new BigDecimal(ActivityGroupSPConfig.getConfig(projectCode).getsDiscount3()).divide(new BigDecimal(3) , 0 , BigDecimal.ROUND_DOWN).multiply(new BigDecimal(goodsNum)).longValue();
			}else if("1".equals(pack)&&goodsNum == 3){
				discount = Long.valueOf(ActivityGroupSPConfig.getConfig(projectCode).getsDiscount3());
			} else {
				discount=origin;
			}
		} else {
			// 团购买
			for (Long goodsId : goodsIds) {
				ActivityGroupbuyGoods goods = activityGroupbuyGoodsService
						.selectByPrimaryKey(goodsId);
				origin = origin + goods.getgPrice();
			}
			if ("1".equals(pack)&&goodsNum > 3) {
//				discount = new BigDecimal(ActivityGroupSPConfig.getConfig(projectCode).getgDiscount3()).divide(new BigDecimal(3) , 0 , BigDecimal.ROUND_DOWN).multiply(new BigDecimal(goodsNum)).longValue();
				discount = Long.valueOf(ActivityGroupSPConfig.getConfig(projectCode).getgDiscount3())  / 3 * goodsNum;
				//除以100，向下取整，再乘以100
				discount = new BigDecimal(discount/100).setScale(0 , BigDecimal.ROUND_DOWN).longValue() * 100;
			}else if("1".equals(pack)&&goodsNum == 3){
				discount = Long.valueOf(ActivityGroupSPConfig.getConfig(projectCode).getgDiscount3());
			} else {
				discount=origin;
			}
		}


		return new RespCalPrice(origin,discount);
	}

	@Transactional
	@Override
	public ResBoolSimpleInfo<String> refundByOrderId(String projectCode,String orderId)throws Exception {
		ActivityGroupbuyOrder order=actGroupbuyOrdService.selectByOrderId(projectCode,orderId);
		if(order.getPayStatus().intValue()!=ActivityGroupBuyOrderEnums.payStatus.SUCCESS.getValue()){
			return ResBoolSimpleInfo.getFailInfo("非支付成功状态");
		}
		if(order.getRefundStatus()!=null ){
			if(order.getRefundStatus().intValue()!=ActivityGroupBuyOrderEnums.refundStatus.FALSE.getValue()){
				return ResBoolSimpleInfo.getFailInfo("不能重复申请");	
			}		
		}else{
			//新建退款申请
			ActivityGroupbuyOrder up=new ActivityGroupbuyOrder();
			up.setActivityGroupbuyOrderId(order.getActivityGroupbuyOrderId());
			up.setRefundId(OrderIdUtil.getSPRefundIdID());
			actGroupbuyOrdService.updateByPrimaryKeySelective(up);			
		}
		
		order=actGroupbuyOrdService.selectByPrimaryKey(order.getActivityGroupbuyOrderId());
		
		//提交申请
		ReqRufundOrder req=new ReqRufundOrder(ActivityGroupSPConfig.getConfig(
				order.getProjectCode()).getSysCode(),order.getProjectCode(),order.getOrderId(),order.getRefundId());
		ResBoolSimpleInfo<Integer> refundRes=zyPayService.refundAllOrder(req);
		if(refundRes.isSuccess()){		
			ActivityGroupbuyOrder up=new ActivityGroupbuyOrder();
			up.setActivityGroupbuyOrderId(order.getActivityGroupbuyOrderId());
			up.setRefundStatus(refundRes.getData());
//			up.setRefundStatus(ActivityGroupBuyOrderEnums.refundStatus.INIT.getValue());
			actGroupbuyOrdService.updateByPrimaryKeySelective(up);
		}else{
			return ResBoolSimpleInfo.getFailInfo(refundRes.getMessage());	
		}
		
		return ResBoolSimpleInfo.getSuccessInfo();
	}

	@Override
	public ResBoolSimpleInfo<String> refundPartByOrderId(String projectCode, String orderId, Long amount) throws Exception {
		ActivityGroupbuyOrder order=actGroupbuyOrdService.selectByOrderId(projectCode,orderId);
		if(order.getPayStatus().intValue()!=ActivityGroupBuyOrderEnums.payStatus.SUCCESS.getValue()){
			return ResBoolSimpleInfo.getFailInfo("非支付成功状态");
		}
		if(order.getRefundStatus()!=null ){
			if(order.getRefundStatus().intValue()!=ActivityGroupBuyOrderEnums.refundStatus.FALSE.getValue()){
				return ResBoolSimpleInfo.getFailInfo("不能重复申请");
			}
		}else{
			//新建退款申请
			ActivityGroupbuyOrder up=new ActivityGroupbuyOrder();
			up.setActivityGroupbuyOrderId(order.getActivityGroupbuyOrderId());
			up.setRefundId(OrderIdUtil.getSPRefundIdID());
			actGroupbuyOrdService.updateByPrimaryKeySelective(up);
		}

		order=actGroupbuyOrdService.selectByPrimaryKey(order.getActivityGroupbuyOrderId());

		//提交申请
		ReqRufundOrder req=new ReqRufundOrder(ActivityGroupSPConfig.getConfig(
				order.getProjectCode()).getSysCode(),order.getProjectCode(),order.getOrderId(),order.getRefundId());
		ResBoolSimpleInfo<Integer> refundRes=zyPayService.refundPartOrder(req,amount);
		if(refundRes.isSuccess()){
			ActivityGroupbuyOrder up=new ActivityGroupbuyOrder();
			up.setActivityGroupbuyOrderId(order.getActivityGroupbuyOrderId());
			up.setRefundStatus(refundRes.getData());
//			up.setRefundStatus(ActivityGroupBuyOrderEnums.refundStatus.INIT.getValue());
			actGroupbuyOrdService.updateByPrimaryKeySelective(up);
		}else{
			return ResBoolSimpleInfo.getFailInfo(refundRes.getMessage());
		}

		return ResBoolSimpleInfo.getSuccessInfo();
	}


	@Override
    public List<GroupSpGoodsVo> findGoods(String projectCode) {
        return activityGroupbuyGoodsService.findGoods(projectCode);
    }

	@Override
	public List<RespCanActivityGroupSPList> findMoreCanActivityGroupBuy(String projectCode, int limit) {
		return actGroupbuyService.findMoreCanActivityGroupBuy(projectCode , limit);
	}

	@Override
	public ResBoolSimpleInfo<RespActivityGroupSPDetail> activityGroupSPDetailById(String projectCode, Long activityGroupId , String openId) {
		ActivityGroupbuy activityGroupbuy = actGroupbuyService.selectByPrimaryKey(activityGroupId);
		if(activityGroupbuy == null){
			throw new BusinessException("找不到改团购信息！");
		}

		//同步当前用户的订单状态
		List<ActivityGroupbuyOrder> userOrders =  actGroupbuyOrdService.getOrderByOpenIdInGroup(projectCode , openId , activityGroupId);
		userOrders.forEach(activityGroupbuyOrder -> {
			try {
				//还在初始化状态的同步
				if(activityGroupbuyOrder.getPayStatus() == ActivityGroupBuyOrderEnums.payStatus.INIT.getValue()){
					this.compensateOrder(activityGroupbuyOrder.getActivityGroupbuyOrderId());
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		});

		activityGroupbuy = actGroupbuyService.selectByPrimaryKey(activityGroupId);

		ResBoolSimpleInfo<RespActivityGroupSPDetail> res=new ResBoolSimpleInfo<>();

		RespActivityGroupSPDetail detail = new RespActivityGroupSPDetail();

		detail.setUserList(actGroupbuyOrdService.selectUserListByGroupBugId(projectCode , activityGroupId));

		if(!StringUtils.isEmpty(openId)){
            detail.setChannel(actGroupbuyOrdService.getChannelInOrder(projectCode , activityGroupId , openId));//渠道
        }

		detail.setTotal(activityGroupbuy.getTotal());
		detail.setCloseTime(activityGroupbuy.getCloseTime());
		detail.setActivityGroupId(activityGroupbuy.getActivityGroupbuyId());
		detail.setGroupType(activityGroupbuy.getGroupType());
		detail.setStatus(activityGroupbuy.getStatus());

		detail.getUserList().forEach(respActivityGroupSPDetailUser -> {
		    //处理手机号码
            respActivityGroupSPDetailUser.setPhone(PhoneUtil.tran4star(respActivityGroupSPDetailUser.getPhone()));
			if(respActivityGroupSPDetailUser.getOpen_id().equals(openId)){
				if(respActivityGroupSPDetailUser.getType() == 0){
					detail.setRelationType(1);//团长
				}else {
					detail.setRelationType(2);//团员
				}
			}
		});
		//游客
		if(detail.getRelationType() == null || (detail.getRelationType() != 1 && detail.getRelationType() != 2)){
			detail.setRelationType(3);
		}

		res.setData(detail);
		res.setSuccess(true);


		return res;
	}

	@Override
	public ResBoolSimpleInfo<String> compensateOrder(
			Long activityGroupbuyOrderId) throws Exception {
		ActivityGroupbuyOrder activityGroupbuyOrder=actGroupbuyOrdService.selectByPrimaryKey(activityGroupbuyOrderId);
		if(activityGroupbuyOrder.getPayStatus()==0){
			ResBoolSimpleInfo<String> res=zyPayService.queryOrder(activityGroupbuyOrder.getOrderId());
			if(res.isSuccess()){
				//更新状态支付成功
				ActivityGroupbuyOrder update=new ActivityGroupbuyOrder();
				update.setActivityGroupbuyOrderId(activityGroupbuyOrder.getActivityGroupbuyOrderId());
				update.setPayStatus(ActivityGroupBuyOrderEnums.payStatus.SUCCESS.getValue());
				actGroupbuyOrdService.updateByPrimaryKeySelective(update);

				//活动状态
				boolean booSta=false;
				ActivityGroupbuy activityGroupbuy=actGroupbuyService.selectByPrimaryKey(activityGroupbuyOrder.getActivityGroupbuyId());
				if(activityGroupbuy.getGroupType().intValue()==ActivityGroupBuyEnums.groupType.SINGLE.getValue()){
					booSta=true;
				}else{
					//判断该活动是否满人
					List<ActivityGroupbuyOrder> succOrderList=actGroupbuyOrdService.findSuccByGroupId(activityGroupbuyOrder.getActivityGroupbuyId());
					if(succOrderList.size()>=ActivityGroupSPConfig.getConfig(activityGroupbuyOrder.getProjectCode()).getNum()){
						booSta=true;
					}
				}			
				if(booSta){
					ActivityGroupbuy upActivityGroupbuy=new ActivityGroupbuy();
					upActivityGroupbuy.setActivityGroupbuyId(activityGroupbuyOrder.getActivityGroupbuyId());
					upActivityGroupbuy.setStatus(ActivityGroupBuyEnums.status.TRUE_SUCCESS.getValue());
					actGroupbuyService.updateByPrimaryKeySelective(upActivityGroupbuy);
				}				
				
			}
		}

		return ResBoolSimpleInfo.getSuccessInfo();
	}

	@Override
	public HSSFWorkbook excelSPOrders(String projectCode, String text, String orderId, Integer payStatus, String phone) {

		HSSFWorkbook excel = new HSSFWorkbook();
		HSSFSheet sheet = excel.createSheet("反三科订单");
		HSSFRow firstRow = sheet.createRow(0);

		String[] titles = new String[] { "订单编号","渠道","类型","姓名","微信昵称","微信头像","团长/团员","手机号","科目","订单价格","下单时间","订单状态","申请退款时间","退款订单号","退款处理状态"};
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell = firstRow.createCell(i);
			cell.setCellValue(titles[i]);
		}

		Map<String, Object> conditions = new HashMap<>();
		Pagination<RespActivityGroupBuyOrderListVo> queryPgn = new Pagination<>();
		conditions.put("projectCode", projectCode);
		if(!org.springframework.util.StringUtils.isEmpty(text)){
			conditions.put("text", text.trim());
		}
		if(!org.springframework.util.StringUtils.isEmpty(orderId)){
			conditions.put("orderId", orderId.trim());
		}
		if(!org.springframework.util.StringUtils.isEmpty(payStatus)){
			conditions.put("payStatus", payStatus);
		}
		if(!org.springframework.util.StringUtils.isEmpty(phone)){
			conditions.put("phone", phone);
		}
		queryPgn.setConditions(conditions);
		queryPgn.setFromRecord(0);
		queryPgn.setToRecord(200000);
		List<RespActivityGroupBuyOrderListVo> list = actGroupbuyOrdService.getAllUserOrder(queryPgn);

		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for (int i = 0; i < list.size(); i++ ) {

			RespActivityGroupBuyOrderListVo r = list.get(i);
			HSSFRow row = sheet.createRow(i + 1);

			int c = 0;
			HSSFCell cell = row.createCell(c++);
			cell.setCellValue(r.getOrderId());

            cell = row.createCell(c++);
            cell.setCellValue(r.getChannel());

			cell = row.createCell(c++);
			cell.setCellValue(r.getGroupTypeStr());

			cell = row.createCell(c++);
			cell.setCellValue(r.getName());

            cell = row.createCell(c++);
            String n = "";
            if(StringUtils.isNotEmpty(r.getWxName())){
                try {
                    n = java.net.URLDecoder.decode(r.getWxName(), "utf-8");
                } catch (UnsupportedEncodingException e) {

                }
            }
            cell.setCellValue(n);

            cell = row.createCell(c++);
            cell.setCellValue(r.getHeadImg());

			cell = row.createCell(c++);
			cell.setCellValue(r.getType().equals("1") ? "团员":"团主");

			cell = row.createCell(c++);
			cell.setCellValue(r.getPhone());

			cell = row.createCell(c++);
			cell.setCellValue(r.getGoodsName());

			cell = row.createCell(c++);
			cell.setCellValue(MathUtils.div(r.getAmount() , 100 , 2));

			cell = row.createCell(c++);
			cell.setCellValue(r.getCreateTime() == null ? "" : formatter.format(r.getCreateTime()));

			cell = row.createCell(c++);
			cell.setCellValue(r.getPayStatusStr());

			cell = row.createCell(c++);
			cell.setCellValue(r.getRefundTime() == null ? "" : formatter.format(r.getRefundTime()));

			cell = row.createCell(c++);
			cell.setCellValue(r.getRefundId());

			cell = row.createCell(c++);
			cell.setCellValue(r.getRefundStatusStr());
		}

		return excel;
	}

}
